package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import tools.Page;
import vo.User;
import dao.UserDao;

public class UserDaoImp extends SimpleJdbcDaoSupport implements UserDao {
	
	private ParameterizedRowMapper<User> rm=new ParameterizedRowMapper<User>(){
		public User mapRow(ResultSet rs,int index)throws SQLException{
			User user=new User();
			user.setId(rs.getInt("id"));
			user.setEmail(rs.getString("email"));
			user.setUserName(rs.getString("userName"));
			user.setPassword(rs.getString("password"));
			user.setQq(rs.getString("qq"));
			return user;
		} 
	};
	
	//CRUD的基本操作===========================================================================================	

	public int AddUser(final User user) {
		final String sql="insert into userinfo(userName,password,email,qq) values(?,?,?,?)";
		KeyHolder keyHoder=new GeneratedKeyHolder();
		getJdbcTemplate().update(new PreparedStatementCreator(){
			public PreparedStatement createPreparedStatement(Connection conn)throws SQLException{
				PreparedStatement ps =conn.prepareStatement(sql);
				ps.setString(1, user.getUserName());
				ps.setString(2, user.getPassword());
				ps.setString(3, user.getEmail());
				ps.setString(4, user.getQq());
				return ps;
			}
		}, keyHoder);
		return keyHoder.getKey().intValue();
	}

	public void deleteUser(int id) {
		String sql="DELETE FROM `userinfo` WHERE (`id`=?)";
		getSimpleJdbcTemplate().update(sql,id);
	}

	public List<User> getAllUser() {
		String sql="select * from userinfo";
		List<User> list =getSimpleJdbcTemplate().query(sql, rm);
		return list.isEmpty()? null:list;
	}

	public User getUserById(int id) {
		String sql="select * from userinfo u where u.id=?";
		List<User> list =getSimpleJdbcTemplate().query(sql, rm, id);
		return list.isEmpty()? null:list.get(0);
	}

	public void updateUser(User user) {
		String sql="update userinfo u set u.userName=?,u.password=?,u.email=?,u.qq=? where u.id=?";
		getSimpleJdbcTemplate().update(sql, user.getUserName(),user.getPassword(),user.getEmail(),
				user.getQq(),user.getId());
		
	}

	public User getUserByLogin(String userName, String password) {
		String sql="select * from userinfo u where u.userName=? and u.password=?";
		List<User> list =getSimpleJdbcTemplate().query(sql, rm,userName,password);
		return list.isEmpty()? null:list.get(0);
	}

	public Page<User> getUserByNameLike(int pageNo,int pageSize,String userName) {
		String sql="select count(*) from userinfo u where u.userName like ?";
		List<Integer> list =getSimpleJdbcTemplate().query(sql, new ParameterizedRowMapper<Integer>(){
			public Integer mapRow(ResultSet rs,int index)throws SQLException{
				return rs.getInt(1);
			}},'%'+userName+'%');
		int count=list.get(0);
		if(count==0)
			return null;
		sql="select * from userinfo where userName like ? limit ?,?";
		int start = (pageNo - 1) * pageSize;
		List<User> list2 =getSimpleJdbcTemplate().query(sql, rm,'%'+userName+'%',start,pageSize);
		Page<User> page=new Page<User>(start,count,pageSize,list2);
		return page;
	}

	public Page<User> getQueryUser(int pageNo, int pageSize) {
		String sql="select count(*) from userinfo";
		List<Integer> list =getSimpleJdbcTemplate().query(sql,new ParameterizedRowMapper<Integer>(){
			public Integer mapRow(ResultSet rs,int index)throws SQLException{
				return rs.getInt(1);
			}});
		int count=list.get(0);
		if(count==0)
			return null;
		sql="select * from userinfo limit ?,?";
		int start = (pageNo - 1) * pageSize;
		List<User> list2 =getSimpleJdbcTemplate().query(sql, rm, start,pageSize);
		Page<User> page=new Page<User>(start,count,pageSize,list2);
		return page;
	}

}
